System and method for multicolumn sorting in a single column

ABSTRACT

A system for multicolumn ordering includes a processor and a data store. The data store stores a table including a plurality of category and data columns. The processor applies key prefixes to column data values of a plurality of the data columns to form and store in the data store a set of prefixed data values. The processor then orders the set of prefixed data values in a first compound column and one or more category columns in a second compound column and responsive to the first and second compound columns selects and stores a subset for further processing.

BACKGROUND OF THE INVENTION

1. Technical Field of the Invention

This invention relates to sorting technology. More particularly, itrelates to multicolumn sorting in one column.

2. Background Art

The IBM Lotus Notes™ program currently provides a multiple column sortcapability for non-restricted document views. Views are dynamicallychanging windows in a Notes database for organizing and locatingdocuments. When the view is embedded and restricted by category, thenthe multiple column sort capability is unavailable. In Notes, anembedded object, such as a view, is an object that becomes part of aNotes document.

One approach to providing multiple column sort capability in embeddedviews restricted by category is with the use of multiple Notes views, atthe cost of large overhead on the system for maintaining these multipleNotes views. Maintenance cost increases when servicing or updatingmultiple views. Another approach is to sort data inline with every queryrequest. There is a need, therefore, to provide improved serverperformance, by providing multicolumn sort capability with fewer viewswhile avoiding having to sort data inline with every query request.

It is an object of the invention to provide an improved system andmethod for sorting on multiple columns when multiple column sortcapability is not available or efficient.

SUMMARY OF THE INVENTION

A system and method for multicolumn ordering by applying key prefixes tocolumn data values of a plurality of data columns to form a set ofprefixed data values; ordering the set of prefixed data values in afirst compound column; and selecting from that ordered set a subset forfurther processing.

In accordance with an aspect of the invention, there is provided acomputer program product configured to be operable for ordering in asingle column a table having a plurality of category and data columns byuse of column key prefixes on column values.

Other features and advantages of this invention will become apparentfrom the following detailed description of the presently preferredembodiment of the invention, taken in conjunction with the accompanyingdrawings.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 is a diagrammatic view illustrating an embedded subsetted viewrestricted by category within the view framework of Notes.

FIG. 2 illustrates the format of a portion of a Notes document tableincluding category columns requester 41 and initiator 43 and a pluralityof data columns 50-58.

FIG. 3 illustrates a view of the document table of FIG. 2, with thecategory columns 41 and 43 combined into an owner column 42.

FIG. 4 illustrates a subsetted view of the table of FIG. 3 restricted toBill as owner (either requester or initiator).

FIG. 5 illustrates a specialized view of the table of FIG. 2 with twosorted compound fields (owner and data) in accordance with the preferredembodiment of the invention.

FIG. 6 is a diagrammatic illustration of the format of a row entry inthe specialized view of the table of FIG. 5.

FIG. 7 illustrates a portion of the specialized view of FIG. 5 selectedto show the application of Notes methods: GetFirstEntry, GetNthEntry,GetAllEntriesByKey, and GetLastEntry.

FIG. 8 illustrates an embedded view derived from the embedded view ofFIG. 4 showing the amount column sorted in descending order inaccordance with the preferred embodiment of the invention.

FIG. 9 is a diagrammatic illustration of formats of row entries in thespecialized view of FIG. 10, illustrating concatenation of data fieldsfor nested sorting in accordance with an alternative embodiment of theinvention.

FIG. 10 illustrates a specialized view showing concatenated data fieldsfor nested sorting.

FIG. 11 is a flow chart representation of the initialization steps of anillustrative embodiment of the invention.

FIGS. 12A through 12F are a flow chart representation of an illustrativeembodiment of the invention for collecting into local arrays columnsorted data for presentation in an embedded view.

DETAILED DESCRIPTION OF PREFERRED EMBODIMENTS

In accordance with the preferred system and method of the invention,multicolumn ordering is accomplished in a single column by use of columnkey prefixes on column values.

Exemplary embodiments of the invention draw upon the methods of the IBMLotus Notes (TM) program.

Referring to FIG. 1, a diagrammatic view illustrates an embeddedsubsetted view 30 within the view framework of Notes restricted bycategory. Non-restricted document view 20 includes category 22 and datacolumns 24, 26. In the prior art, when selecting portion 28 restrictedto a specific category 22 for display in embedded view 30, the abilityto sort data on columns 24 or 26 is lost. In accordance with the presentinvention, data columns 24 and/or 26 may be sorted in ascending ordescending mode.

Referring to FIG. 2, the format of a portion of a Notes document table40 is illustrated including category columns requester 41 and initiator43, document reference number column 44, and a plurality of data columnsincluding number of items 50, creation date 52, dollar amount 54, firstsupplier 56, and first item 58.

Referring to FIG. 3, a modified view of Notes document table 40illustrates a table of data for a collection of Notes documents, inwhich the requester 41 and initiator 43 columns have been combined intoan owner column 42, and data columns including document reference number44, number of items 50, creation date 52, dollar amount 54, firstsupplier 56 and first item 58. In this example, Bill is both requester41 and initiator 43 of document reference number 6, and the requester 41and initiator 43 are different for the other illustrated entries intable 40.

Referring to FIG. 4, a subsetted view of the table of FIG. 3 isillustrated which is restricted to include only rows or entries in whichBill is the owner 42 (that is, either requester 41 or initiator 43 ).Primary key 62 and column keys 64, 66 will be described hereafter inconnection with sorting and nested sorting, respectively.

Referring to FIG. 5, a specialized view 80 of the table of FIG. 2 isillustrated with two sorted compound fields (owner 42 and data 84 ) inaccordance with the preferred embodiment of the invention.

In order to provide the ability to sort on several columns 41-58 in aNotes view, including at least one restricting column 41, 43 and aplurality of data columns 50-58, a first sorted compound column (herein,owner 42) is created from the restricting columns 41, 43 and a secondsorted compound column 84 is created from the data columns 50-58 andprefix data, or column keys, 64.

Referring to FIG. 6, the format of a row entry 90 in the specializedview of the table of FIG. 5 is shown. A first compound column 42 issorted on primary key 62 which, in the example of FIG. 4, includes ownercolumn 42. A second compound column 94 is sorted on column key 64,including prefix values 70-74 and corresponding data columns 54-58, aswill be illustrated hereafter. Referring to FIG. 5, before sorting thesecond compound column 84, a key 64 (preferably in character format) isprefixed to each data value 98, which key identifies the specific datacolumn 54-58 for that data value. Since the prefix is character informat, the data values 54-58 are converted to some appropriatecharacter representation to ensure proper sorting. In this manner, adate column may be formatted as year, month and day, or YYYYMMDD. Numbervalues would be left padded to represent the integer values by numbersof an appropriate length for proper ordering (so that 002 comes before010, else 2 would come after 10 based on a comparison of firstcharacters). A key/data delimiter, such as colon “:” 96, is insertedbetween column key 64 and data value 98.

View 80 is defined to 'show multiple values as separate entries'. Sothat instead of one document record in the view with six item columnvalues, there are six separate document records with single values in asorted second column 84.

The resulting view 80 shows a first column 42 that is sorted by owner(requester 41 or initiator 43), and a second column 84 that is sorted bydata type 70, 72, 74 first and within data type by the value 98 of thedata type. In this manner, for a table of N data columns 54-58 (thosedata columns selected for sort from a table which may include many moredata columns, such as columns 50 and 52), N different sorts are combinedinto one column 84. In the example of FIGS. 4 and 5, N=3.

Referring to FIG. 7, a portion of the specialized view of FIG. 5 isselected to show the application of Notes methods: GetFirstEntry 110,GetNthEntry 112, GetAllEntriesByKey 114, and GetLastEntry 116.

For example, view 80 is opened by a Notes Agent to executeGetAllEntriesByKey 114. As input to this request, the owner name 42 isthe first key 62 to match on, and the second key is the prefix value 64so that the second column 84 is matched (not an exact match) on just theprefix 64. This process extracts from view 80 a unique set of documentsthat are sorted as illustrated by view 100 by the compound valueprefix|data 64|98.

Referring to FIG. 8, an embedded view 120 derived from the embedded viewof FIG. 4 shows the amount column 54 sorted in descending order inaccordance with the preferred embodiment of the invention.

The above described GetAllEntriesByKey method 114 results in aNotesViewEntryCollection which includes a count property that yields thenumber of documents in the result set 120. (In this example, there are15 rows, and the count property is 15.) A NotesViewEntryCollectionrepresents a collection of view entries of type document sorted in vieworder.

GetFirstEntry 110, GetLastEntry 116, GetNextEntry, GetNthEntry 112 andGetPrevEntry methods allow navigation to a set of documents 108 that areof interest. Since the document set is ordered (as illustrated from rows102 through 106 of FIG. 7), they are sorted in the same order, eitherascending or descending, and these methods and the count propertyprovide sufficient tools to identify the set of documents from eitherthe top 102 of the list or the bottom 106 for posting as view 120 backto the user.

Referring to FIGS. 9 and 10, concatenation of data fields 54, 56 fornested sorting in accordance with an alternative embodiment of theinvention is illustrated. In order to post back to the user a view inwhich a plurality of columns 54, 56 are sorted in nested fashion, aprefix value 76 may be appended to a concatenation of that plurality ofcolumns 54, 56, and then sorted as above along with column keys 78, 144and data 58, 146, respectively, into second compound column 142 ofspecialized view 140. Rows 138 illustrated the sorted concatenated datavalues.

Referring to FIGS. 11 and 12A through 12F in connection with FIG. 7 andTable 1, an exemplary embodiment of the invention is illustrated bypseudo code and flow charts. As used in Table 1, a Notes session is theroot of the Domino Objects containment hierarchy, providing access tothe other Domino objects, and represents the Domino environment of thecurrent program. A specific view is a named Notes view. A view entrycollection represents a collection of view entries, selected accordingto specific criteria. This class mainly provides a collection ofdocuments in a view-sorted order. In the agent, document information isbeing collected. How many documents this is done for depends on a pagesize. The agent is filling this space/page with document data-thereforethe reference to a data page. A local array is a data structure local tothe processing agent to serve as the collection area for the view dataof interest. Notes agents are stand-alone programs that perform aspecific task in one or more databases. In steps 220, 222, the “specificmethod” refers to GetLastEntry and GetFirstEntry versus GetNthEntry. Thedocument entry in step 244 refers to a Notes View Entry, and representsa view entry. A view entry represents a row in a view. In this case, rowreflects a Notes Document. For a Notes View Entry, the underlyingdocument may be accessed via the property “Document” of the Notes ViewEntry. Thus, the document referred to in step 246 corresponds to a lineof data from the view. At step 266, the needed data has been capturedinto local arrays. It is expected that the code following this pointwill format the data and present it to a Web page. TABLE 1 PSEUDO CODEREPRESENTATION OF AN ALGORITHM FOR MULTICOLUMN SORTING IN ONE COLUMN 1Step 202: Define a Notes Session. 2 Step 204: For the Notes Session,define a Notes Database 3 link to the current database. 4 Step 206:Define a Notes View to a specific view in the 5 current database. 6 View 100 has 2 sorted columns 42, 94. 7   First column 42 ismultivalued - represents unique 8   document owners (in this case, Bill)9   Second column 94 is multivalued - all data has 10   been convertedin the document to character 11   appropriately padded on the front toallow for 12   correct sorting alignments 13   within the second column94, every value is 14   uniquely prefixed with a column number 64 15  followed by a colon 96: 16    01:yyyy/mm/dd (date) 17    02:$$$$$$$$$$(dollar value front padded 18 with zeros to maximum $ value) 19   03:nnnnnnnnn (number front padded with 20 zeros to maximum size) 21   04:text 22  The view 100 is defined to break up these multivalued 23 entities into separate view rows. 24 Thus, a multicolumn, bidirectional(done with programming) 25 sort is provided in a single column 94. Thealgorithm below 26 extracts the required data from the view 100. 27 Step210: Get the input parameters: sort direction, column 28 index, desiredpage number, page size, and owner. 29 Step 212: Based on page size anddesired page number, 30 compute starting record number. 31 Step 214:Define keys to restrict view. 32  First key 42 is full key representingone of the 33  document owners (in this example: Bill). 34  Second keyis a partial one having the prefix 64 35  corresponding to the column ofinterest (e.g. “02”) 36  (colon 96 is used in prefix 64 to distinguishit from 37  remainder of column data 98) 38 Step 216: Define a Viewentry collection using defined keys 39 42, 64. Resulting view entrycollection is a restricted 40 list 100 where the owner matches one ofthe owners (e.g., 41 Bill), sorted on the indicated column 94). 42 Step218: Note the View entry collection result count (In 43 this example,15, the number of rows sorted on key 02 in 44 Figure 5). 45 Step 220:Based on direction and starting row, set pointer 46 for first displaydocument. 47  (If at top or bottom of collection, quicker to use 48 specific method rather than getting Nth entry.) 49 Step 222: If (SortDirection is descending) Then 50  Step 224: If (Starting Record is 1)Then ‘ First or 51  bottom entry 106 in collection 52 53   Step 226: SetDocumentEntry = 54   ViewCollection.GetLastEntry( ) 55  Else ‘ Middle ofcollection 104 / reverse order 56   Step 228: Set DocumentEntry = 57  ViewCollection.GetNthEntry( CollectionCount less 58   StartingRecord )59  End If 60 Else ‘ Sort Direction is ascending 61  Step 230: If(Starting Record is 1) Then ‘ First or top 62  entry 102 in collection63   Step 232: Set DocumentEntry = 64   ViewCollection.GetFirstEntry( )65  Else ‘ Middle 104 of collection 66   Step 234: Set DocumentEntry =67   ViewCollection.GetNthEntry( StartingRecord ) 68  End If 69 End If70 Step 236: Create array structure to hold required document 71 data.72 Step 238: Initialize documents captured to zero. 73 Step 240: Resetthe next data page flag. 74 Step 240: Reset the page full flag. 75 Steps242-264: Do While (DocumentEntry Is Available) ‘ If 76 we have aDocumentEntry to look at, start the look 77 78  Step 244: For theDocumentEntry, get the Document. 79  Step 246: If (Document isavailable) Then 80   Step 248: If (Page full flag is set, then 81  document represents start of another data page) 82   Then 83    Step264: Set next data page flag to 84    indicate that there is more dataafter this 85    page 86    Exit Do 87   End If 88 89   Step 250: Getrequired document data into local 90   arrays for later presentation 91  Step 252: Increment counter of documents captured 92   for currentdata page. 93   Step 254: If (Documents captured equals the page 94  size) Then 95    Step 256: Set page full flag 96   End If 97  End If98  Based on sort direction, get the next DocumentEntry 99  Step 258: If(Sort Direction is Descending) Then 100   Step 260: Get PreviousDocumentEntry 101  Else 102   Step 262: Get Next DocumentEntry 103  EndIf 104 105 Loop 106 Step 266: Required data (if present) is in localarrays to 107 further process as required.

By sorting on two columns 42, 84, when view restrictions are applied, aview 100 is presented which in a first step includes only a specifiedperson's (Bill) data. In a second step, a Notes agent ripples throughthe clustered data to pull off what is needed to present to the user aview of his data in sorted order. The key 64 applied to the data values98 collapses multiple columns 50-58 into one column 94.

In accordance with the preferred embodiment of the invention, onedatabase is conceptualized. However, it may be implemented with Ndatabases, in which case it would only be required to keep track of anext record to retrieve based on the largest (or smallest) recordremaining in the N databases.

Advantages over the Prior Art

It is an advantage of the invention that there is provided an improvedsystem and method for sorting on multiple columns when multiple columnsort capability is not available or efficient.

Alternative Embodiments

It will be appreciated that, although specific embodiments of theinvention have been described herein for purposes of illustration,various modifications may be made without departing from the spirit andscope of the invention. In particular, it is within the scope of theinvention to provide a computer program product or program element, or aprogram storage or memory device such as a solid or fluid transmissionmedium, magnetic or optical wire, tape or disc, or the like, for storingsignals readable by a machine, for controlling the operation of acomputer according to the method of the invention and/or to structureits components in accordance with the system of the invention.

Further, each step of the method may be executed on any generalcomputer, such as IBM Systems designated as zSeries, iSeries, xSeries,and pSeries, or the like and pursuant to one or more, or a part of oneor more, program elements, modules or objects generated from anyprogramming language, such as C++, Java, Pl/1, Fortran or the like. Andstill further, each said step, or a file or object or the likeimplementing each said step, may be executed by special purpose hardwareor a circuit module designed for that purpose.

Accordingly, the scope of protection of this invention is limited onlyby the following claims and their equivalents.

1-4. (canceled)
 5. A computer implemented method for improved serverperformance in response to a query request by presenting to a user a setof documents in an embedded view derived by multicolumn sorting in onecolumn of a table including at least one category column and a pluralityof data columns in a digital storage device, comprising: ordering saidcategory column in a first compound column; converting non-characterdata values in said data columns to character format; applying a columnkey and delimiter to said data values; ordering said data values in asecond compound column; defining a primary key on said first compoundcolumn and a column key on said second compound column; responsive tosaid primary key and said column key, collecting into an embedded viewto said user a subset of said table; and navigating to a documentselected by said user from said embedded view.
 6. The method of claim 5,further comprising ordering a plurality of said category columns in saidfirst compound column.
 7. The method of claim 5, further comprising:concatenating at least two of said data columns to form a set ofconcatenated data values; applying said column key and delimiter to saidconcatenated data values to form a set of concatenated prefixed datavalues; and ordering said data values and said concatenated data valuesin said second compound column.
 8. A system for improved serverperformance in response to a query request by presenting to a user a setof documents in an embedded view derived by multicolumn ordering in asingle column of a table of including at least one category column and aplurality of data columns, comprising: means for ordering said categorycolumn in a first compound column; means for converting non-characterdata values in said data columns to character format; means for applyinga column key and delimiter to said data values; means for ordering saiddata values in a second compound column; means for defining a primarykey on said first compound column and a column key on said secondcompound column; and means responsive to said primary key and saidcolumn key for collecting into an embedded view to said user a subset ofsaid table means for navigating to a document selected by said user fromsaid embedded view. 9-15. (canceled)
 16. A computer program product forimproved server performance in response to a query request by presentingto a user a set of documents in an embedded view derived by multicolumnsorting in one column of a table including at least one category columnand a plurality of data columns in a digital storage device, saidcomputer program product comprising: a computer readable medium; firstprogram instructions for ordering said category column in a firstcompound column; second program instructions for convertingnon-character data values in said data columns to character format;third program instructions for applying a column key and delimiter tosaid data values; fourth program instructions for ordering said datavalues in a second compound column; fifth program instructions fordefining a primary key on said first compound column and a column key onsaid second compound column; sixth program instructions, responsive tosaid primary key and said column key, for collecting into an embeddedview to said user a subset of said table; and seventh programinstructions for navigating to a document selected by said user fromsaid embedded view; and wherein said first, second, third, fourth,fifth, sixth, and seventh program instructions are recorded on saidcomputer readable medium.